![]() |
Java Database Programming with JDBC
by Pratik Patel Coriolis, The Coriolis Group ISBN: 1576100561 Pub Date: 10/01/96 |
Previous | Table of Contents | Next |
Predefining a data object is also useful for making sure that a certain entry in a column matches the data we expect to find there. For example, our empno field should contain a number. If it doesnt, performing a check of that data will alert us to the error. These checks can exist in the actual table definition, but its efficient to localize a check in a domain. Hence, we can add a check to our employee number domain:
CREATE DOMAIN EMP_NUMBER AS CHAR(5) CHECK (VALUE IS NOT NULL);
Now our domain automatically checks for any null entries in columns defined as EMP_NUMBER. This statement avoids problems that crop up from non-existent entries, as well as allowing us to catch any rogue SQL queries that add an incorrect (those that do not set the employee number) entry to the table.
Creating a table in SQL is really pretty easy. The one thing you need to keep in mind is that you should define the referenced table, in this case EMPLOYEE, before defining the referencing table, CONFIDENTIAL. The following code creates the EMPLOYEE table shown in Figure 2.2:
CREATE TABLE EMPLOYEE ( empno CHAR(5) PRIMARY KEY, lastname VARCHAR(20) NOT NULL, firstname VARCHAR(20) NOT NULL, function VARCHAR(20) NOT NULL, department VARCHAR(20) );
We also could have easily incorporated the domain that we defined earlier into the creation of the table, as shown here:
CREATE DOMAIN EMP_NUMBER AS CHAR(5) CHECK (VALUE IS NOT NULL); CREATE TABLE EMPLOYEE ( empno EMP_NUMBER PRIMARY KEY, lastname VARCHAR(20) NOT NULL, firstname VARCHAR(20) NOT NULL, function VARCHAR(20) NOT NULL, department VARCHAR(20) );
I can hear you now, Whats this VARCHAR data type? SQL has two defined string types: CHAR and VARCHAR. The RDBMS allocates exactly the amount of space you specify when you use a CHAR data type; when you set an entry that is defined as a CHAR(N) to a string smaller than the size of N, the remaining number of characters is set to be blank. On the other hand, VARCHAR simply stores the exact string entered; the size you have specified is strictly a limit on how big the entered value can be.
We also see the NOT NULL directive again, which institutes the check on the specific column entry. We discussed primary and foreign keys earlier, now lets see how we actually implement them. Note that you should define the referenced table before defining the referencing table.
Now its time to create the CONFIDENTIAL table. This table uses the empno attribute of the EMPLOYEE table as its primary key, via the REFERENCES keyword.
CREATE DOMAIN EMP_NUMBER AS CHAR(5) CHECK (VALUE IS NOT NULL); CREATE TABLE CONFIDENTIAL ( empno EMP_NUMBER PRIMARY KEY, homeaddress VARCHAR(50), homephone VARCHAR(12), salary DECIMAL, FOREIGN KEY ( empno ) REFERENCES EMPLOYEE ( empno ) )
We have tied the empno field in the CONFIDENTIAL table to the empno field in the EMPLOYEE table. The fact that we used the same name, empno, is a matter of choice rather than a matter of syntax. We could have named the empno field whatever we wanted in the CONFIDENTIAL table, but we would need to change the first field referred to in the FOREIGN KEY declaration accordingly.
Database management often requires you to make minor modifications to tables. However, careful planning can help you keep these alterations to a minimum. Lets begin by dropping, or removing, a table from a database:
DROP TABLE EMPLOYEE;
This is all we have to do to remove the EMPLOYEE table from our database. However, if the table is referenced by another table, as is the case with the CONFIDENTIAL table, a RDBMS may not allow this operation to occur. In this situation, you would have to drop any referencing tables first, and then rebuild them without the referencing.
Altering a table definition is as straightforward as dropping a table. To remove a column from a table, issue a command like this:
ALTER TABLE EMPLOYEE DROP firstname;
Of course, if this column is part of the tables key, you wont be able to remove it. Also, if the column is referenced by another table, or there is another column in any table that is dependent on this column, the operation is not allowed.
To add a column to a table, run a query like this:
ALTER TABLE CONFIDENTIAL ADD dateofbirth DATE NOT NULL;
You can also make multiple alterations at one time with the ALTER clause.
Previous | Table of Contents | Next |